use std::{borrow::Cow, ops::Deref};

use sqlx::{Executor, Row, SqliteConnection};

use crate::error::{AppErr, SqlxErr};

use super::get_pool;

const SQL_TRACK_INFO_CREATE: &'static str = r#"
    CREATE TABLE IF NOT EXISTS tb_track_info(
        id INTEGER PRIMARY KEY AUTOINCREMENT, 
        track_id INTEGER NOT NULL, 
        cmd_id INTEGER NOT NULL, 
        data TEXT NOT NULL
    )
"#;

const SQL_TRACK_CREATE: &'static str = r#"
    CREATE TABLE IF NOT EXISTS tb_track(
        id INTEGER PRIMARY KEY AUTOINCREMENT, 
        name TEXT NOT NULL,
        UNIQUE(name)
    )
"#;

pub async fn init() -> Result<(), SqlxErr> {
    let pool = get_pool();
    pool.execute(SQL_TRACK_INFO_CREATE).await?;
    pool.execute(SQL_TRACK_CREATE).await?;
    Ok(())
}

pub struct TrackCmd {
    pub cmd_id: u32,
    pub data: Cow<'static, str>,
}

pub async fn query_all() -> Result<Box<[String]>, AppErr> {
    let rows = sqlx::query("SELECT name FROM tb_track")
        .fetch_all(get_pool())
        .await?;
    let ret: Box<[String]> = rows.into_iter().map(|row| row.get(0)).collect();
    Ok(ret)
}

pub async fn is_exists(name: &str) -> Result<bool, SqlxErr> {
    let ret = sqlx::query("SELECT id FROM tb_track WHERE name = ?")
        .bind(name)
        .fetch_optional(get_pool())
        .await?;
    Ok(ret.is_some())
}

pub async fn query_with_name(name: &str) -> Result<Box<[TrackCmd]>, AppErr> {
    let rows = sqlx::query(
        r#"
        SELECT id, track_id, cmd_id, data FROM tb_track_info 
        WHERE track_id 
        IN (SELECT id FROM tb_track WHERE name = ?) 
        ORDER BY id ASC
    "#,
    )
    .bind(name)
    .fetch_all(get_pool())
    .await?;

    if rows.is_empty() {
        return Err(AppErr::Static("没有找到对应的轨迹数据"));
    }

    let mut vec = Vec::with_capacity(rows.len());

    for row in rows {
        let cmd = TrackCmd {
            cmd_id: row.get(2),
            data: Cow::Owned(row.get(3)),
        };
        vec.push(cmd);
    }

    Ok(vec.into_boxed_slice())
}

pub async fn save(name: &str, tracks: &[TrackCmd]) -> Result<(), SqlxErr> {
    let mut tx = get_pool().begin().await?;

    delete_on_exists(&mut tx, name).await?;

    let ret = sqlx::query("INSERT INTO tb_track(name) VALUES (?)")
        .bind(name)
        .execute(&mut *tx)
        .await?;
    let id = ret.last_insert_rowid();

    for info in tracks {
        sqlx::query("INSERT INTO tb_track_info(track_id, cmd_id, data) VALUES (?, ?, ?)")
            .bind(id)
            .bind(info.cmd_id)
            .bind(info.data.deref())
            .execute(&mut *tx)
            .await?;
    }

    tx.commit().await?;
    Ok(())
}

async fn delete_on_exists(conn: &mut SqliteConnection, name: &str) -> Result<(), SqlxErr> {
    sqlx::query(
        "DELETE FROM tb_track_info WHERE track_id IN (SELECT id FROM tb_track WHERE name = ?)",
    )
    .bind(name)
    .execute(&mut *conn)
    .await?;

    sqlx::query("DELETE FROM tb_track WHERE name = ?")
        .bind(name)
        .execute(&mut *conn)
        .await?;

    Ok(())
}
